
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Kohli
 */
public class ConnectToDatabase
{

    private Connection connection = null;
    private String connectionURL;
    private Statement stmt;
    private ResultSet results;
    
    ConnectToDatabase()
    {
        // connectionURL = "jdbc:derby://localhost:1527/C:\\Users\\Paul\\Programming Tools\\tableLocations\\ABS";
        connectionURL = "jdbc:derby://localhost:1527/C:\\Users\\Kohli\\.netbeans-derby\\ABS";
//        connectionURL = "jdbc:derby://localhost:1527/C://Users//Kanak//.netbeans-derby//ABS1";

        
        try
        {
            connection = DriverManager.getConnection( connectionURL );
        }
        catch (SQLException e)
        {
            System.out.println("Connection failed");
            e.printStackTrace();
        }

    }

    public boolean checkLoginInfo(String userId, String password)
    {
        boolean loginFlag = false;
        
        try
        {
             stmt = connection.createStatement();
             results = stmt.executeQuery("select EMAILADDRESS, PASS from USERPROFILE");

             while(results.next())
             {
                  if (userId.equals(results.getString(1)) && password.equals(results.getString(2)))
                  {
                      JOptionPane.showMessageDialog(null, "You have successfully logged in. \n",
                                                          "Login Successful", JOptionPane.INFORMATION_MESSAGE);

                      loginFlag = true;
                      break;
                  }
             }

             if (loginFlag == false)
             {
                 JOptionPane.showMessageDialog(null, "Invalid username or password. Please try again. \n",
                                                     "Login Failed", JOptionPane.ERROR_MESSAGE);
             }


             results.close();
             stmt.close();
             connection.close();
        }

        catch (SQLException e)
        {
            System.out.println("Couldn't create query connection.");
            e.printStackTrace();
        }

        return loginFlag;
    }



    public void addUserProfile(UserProfile user)
    {
        try
        {
            stmt = connection.createStatement();
            
            stmt.executeUpdate("insert into USERPROFILE values ('" + user.getFirstName() + "', '" +
                              user.getLastName()+ "', '" + user.getEmail() +  "', '" + user.getPassword() +
                               "', '" + user.getPhone() +  "', '" + user.getStreetAddress() +  "', '"  +
                               user.getCity() +  "', '" + user.getState() +  "', '"  + user.getCountry() +
                                "', " + user.getZip() + ")");

            stmt.close();
            connection.close();
        }

        catch (SQLException e)
        {
            System.out.println("Couldn't create query connection.");
            e.printStackTrace();
        }
    }

    private long computePrice(java.util.Date today, java.sql.Date departureDate, int availableSeats)
    {

        long price = 0;

        long diff = departureDate.getTime() - today.getTime();
        diff = diff/(1000 * 60 * 60 * 24);
        System.out.println(diff);

        if (diff == 0 || diff == 1)
            price += 500;
        else if (diff <= 15)
            price += 300;
        else if (diff <= 30)
            price += 150;
        else if (diff <= 45)
            price += 100;

        if (availableSeats > 90)
            price -= 100;
        else if (availableSeats > 80)
            price -= 90;
        else if (availableSeats > 70)
            price -= 80;
        else if (availableSeats > 60)
            price -= 70;
        else if (availableSeats > 50)
            price -= 60;
        else if (availableSeats > 40)
            price -= 50;
        else if (availableSeats > 30)
            price -= 40;
        else if (availableSeats > 20)
            price -= 30;
        else if (availableSeats > 10)
            price -= 20;
        else
            price -= 10;

        // sunday = 0, friday =  5, saturday = 6
        if (departureDate.getDay() == 0 || departureDate.getDay() == 5 || departureDate.getDay() == 6)
            price += 50;
        else
            price += 100;

        // january and december
        if (departureDate.getMonth() == 0 || departureDate.getMonth() == 11)
            price += 100;
        // june, july, and august
        else if (departureDate.getMonth() == 5 || departureDate.getMonth() == 6 || departureDate.getMonth() == 7)
            price += 50;
        else
            price += 25;

        return price;
    }

    public ResultSet flightinfo(GetFlightInfo info, JTable table)
    {
        DefaultTableModel tableModel = (DefaultTableModel) table.getModel();

        Date today = new Date();
        long price;
        ResultSet tempResults;
        Statement tempStmt;

        try
        {
            System.out.println("reached flight info");
            stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            tempStmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            stmt.executeUpdate("Alter table Flight ADD Price REAL");
            stmt.executeUpdate("Alter table Flight ADD Class CHAR(1)");
            
            tempStmt.executeUpdate("Create table temp (Flight_Number int, Flight_Name VARCHAR(20), Airport_code VARCHAR(10), " +
                         "Dtime date, Atime date, From_airportcode VARCHAR(10),	To_airportcode VARCHAR(10), " +
                         "Avail_seats INT, Price REAL, Class CHAR(1))");

            results = stmt.executeQuery("select * from Flight where FROM_AIRPORTCODE='"+info.getdepartingcode()+"'AND TO_AIRPORTCODE='"+info.getarrivalcode()+"'AND AVAIL_SEATS >="+info.getnumberofpassenger()+" AND ATIME='"+info.getdepartingon()+"'");
            tempResults = tempStmt.executeQuery("select * from temp where FROM_AIRPORTCODE='"+info.getdepartingcode()+"'AND TO_AIRPORTCODE='"+info.getarrivalcode()+"'AND AVAIL_SEATS >="+info.getnumberofpassenger()+" AND ATIME='"+info.getdepartingon()+"'");

            while (results.next())
            {
                java.sql.Date departureDate = java.sql.Date.valueOf(results.getString("DTIME"));

                price = computePrice(today, departureDate, Integer.parseInt(results.getString("AVAIL_SEATS")));
                results.updateLong("PRICE", (price + 200));
                results.updateString("CLASS", "A");
                results.updateRow();

                tempResults.moveToInsertRow();
                tempResults.updateString("FLIGHT_NUMBER", results.getString("FLIGHT_NUMBER"));
                tempResults.updateString("FLIGHT_NAME", results.getString("FLIGHT_NAME"));
                tempResults.updateString("AIRPORT_CODE", results.getString("AIRPORT_CODE"));
                tempResults.updateString("DTIME", results.getString("DTIME"));
                tempResults.updateString("ATIME", results.getString("ATIME"));
                tempResults.updateString("FROM_AIRPORTCODE", results.getString("FROM_AIRPORTCODE"));
                tempResults.updateString("TO_AIRPORTCODE", results.getString("TO_AIRPORTCODE"));
                tempResults.updateString("AVAIL_SEATS", results.getString("AVAIL_SEATS"));
                tempResults.updateLong("PRICE", (price + 100));
                tempResults.updateString("CLASS", "B");
                tempResults.insertRow();
            }

            results.close();
            tempResults.close();
            tempStmt.close();

            results = stmt.executeQuery("select * from Flight where FROM_AIRPORTCODE='"+info.getdepartingcode()+"'AND TO_AIRPORTCODE='"+info.getarrivalcode()+"'AND AVAIL_SEATS >="+info.getnumberofpassenger()+" AND ATIME='"+info.getdepartingon()+"' UNION ALL " +
                                        "select * from temp where FROM_AIRPORTCODE='"+info.getdepartingcode()+"'AND TO_AIRPORTCODE='"+info.getarrivalcode()+"'AND AVAIL_SEATS >="+info.getnumberofpassenger()+" AND ATIME='"+info.getdepartingon()+"' ORDER BY 9");

            while(table.getRowCount() != 0){
                tableModel.removeRow(0);
            }
            while (results.next())
            {
                System.out.println("Departure Time: " + results.getString("DTIME") +
                                                         "        Arrival Time: " + results.getString("ATIME") +
                                                         "\nFlight Number: " + results.getString("FLIGHT_NUMBER") );

                    tableModel.addRow(new Object[] {"Departure Time: " + results.getString("DTIME") +
                                                         "        Arrival Time: " + results.getString("ATIME") +
                                                         "\nFlight Number: " + results.getString("FLIGHT_NUMBER") +
                                                         "\nDeparting From: " + results.getString("FROM_AIRPORTCODE") +
                                                         "               Arriving At: " + results.getString("TO_AIRPORTCODE") +
                                                         "\nClass: " + results.getString("class") +
                                                         "         Price per passenger: $" +
                                                         (Float.parseFloat(results.getString("price"))) + "  Best Price in Town!!"});
            }

            stmt.executeUpdate("ALTER table Flight DROP PRICE");
            stmt.executeUpdate("ALTER table Flight DROP CLASS");
            stmt.executeUpdate("DROP table temp");
            stmt.close();
            connection.close();
        }


        catch (SQLException e)
        {
            System.out.println("Couldn't create query connection.");
            e.printStackTrace();
        }

        return results;
    }
  public void addPaymentInfo(PaymentInformation pay)
    {
        try
        {
            stmt = connection.createStatement();
            System.out.println("it's working!!");
            
            stmt.executeUpdate("insert into CreditPAYMENT values('" + pay.getfirstname()+ "','" + pay.getlastname()+"','" + pay.gettype()+"','" + pay.getCardNumber()+"','" + pay.getexpirationDate()+"'," + pay.getSecurityNumber()+" )");

            connection.close();
        }

        catch (SQLException e)
        {
            System.out.println("Couldn't create query connection.");
            e.printStackTrace();
        }
    }
public void addBankPaymentInfo(BankInformation pay2)
    {
        try
        {
            stmt = connection.createStatement();
            System.out.println("it's working!!");

            stmt.executeUpdate("insert into PaymetRouting values('" + pay2.getfirstname()+ "','" + pay2.getlastname()+"','"+pay2.getbankaccount()+"','"+pay2.getroutingnumber()+"')");

            connection.close();
        }

        catch (SQLException e)
        {
            System.out.println("Couldn't create query connection.");
            e.printStackTrace();
        }
    }
public void addPayPalPaymentInfo(PaypalInfo pay1)
    {
        try
        {
            stmt = connection.createStatement();
            System.out.println("it's working!!");

            stmt.executeUpdate("insert into Paypal values('" + pay1.getfirstname()+ "','" + pay1.getlastname()+"','"+ pay1.getpaypalaccount()+"','" + pay1.getpaypalpassword()+"' )");

            connection.close();
        }

        catch (SQLException e)
        {
            System.out.println("Couldn't create query connection.");
            e.printStackTrace();
        }
    }
}

